<?php

/**
 * A search criteria class which couples with the ORM class.
 * @package Nina
 * @author Chris Corbyn, John Main <john@thedaddy.org>
 */
class Nina_Orm_Criteria
{
	/*! Comparison operators */
	const LIKE = "LIKE";
	const LIKE_WILD = "LIKE WILD";
	const NOT_LIKE = "NOT LIKE";
	const EQUAL = "=";
	const EQUALS = self::EQUAL;
	const FIELD_COMPARISON = "===";
	const MATCH = "MATCH";
	const MATCH_PHRASE = "MATCH PHRASE";
	const MATCH_BOOLEAN = "MATCH BOOLEAN";
	const NOT_EQUAL = "<>";
	const NOT_EQUALS = self::NOT_EQUAL;
	const GREATER_THAN = ">";
	const GREATER = self::GREATER_THAN;
	const LESS_THAN = "<";
	const LESS = self::LESS_THAN;
	const GREATER_OR_EQUAL = ">=";
	const GREATER_THAN_OR_EQUAL = self::GREATER_OR_EQUAL;
	const LESS_OR_EQUAL = "<=";
	const LESS_THAN_OR_EQUAL = self::LESS_OR_EQUAL;
	const IS_NOT = "IS NOT";
	const NOT = self::IS_NOT;
	const IS = "IS";
	const IN = "IN";
	const NOT_IN = "NOT IN";
	const BETWEEN = "BETWEEN";
	const NOT_BETWEEN = "NOT BETWEEN";
	
	/*! Logical operators */
	const OP_AND = "AND";
	const OP_OR = "OR";
	
	/*! Ordering operators */
	const ORDER_ASC = "ASC";
	const ORDER_ASCENDING = self::ORDER_ASC;
	const ORDER_DESC = "DESC";
	const ORDER_DESCENDING = self::ORDER_DESC;
	
	/**
	 * Values to bind when asked to commit().
	 * @var array
	 */
	protected $bind = array(
		"where" => array(),
		"having" => array()
	);
	/**
	 * Criteria information for each field added.
	 * @var array
	 */
	protected $fields = array(
		"where" => array(),
		"having" => array()
	);
	/**
	 * Stored joins in the search criteria.
	 * @var array
	 */
	protected $joins = array();
	/**
	 * Tables used in this criteria.
	 * @var array
	 */
	protected $tables = array();
	/**
	 * Subqueries used as tables in this criteria.
	 * @var array
	 */
	protected $table_subqueries = array();
	/**
	 * The offset to take results from if LIMIT is applied.
	 * @var int
	 */
	protected $offset = false;
	/**
	 * Limit value.
	 * @var int
	 */
	protected $limit = false;
	/**
	 * Ordering information.
	 * @var array
	 */
	protected $order = array();
	/**
	 * Ordering information.
	 * @var array
	 */
	protected $nat_order = array();
	/**
	 * Grouping information.
	 * @var array
	 */
	protected $group = array();
	/**
	 * Special select information (min, max, count etc)
	 * @var array
	 */
	protected $special_selects = array();
	/**
	 * The database access object.
	 * @var Nina_Db
	 */
	protected $db;
	
	public static function dateAdd($field, $interval, $time_span) {
		return "DATE_ADD($field, INTERVAL $interval $time_span)";
	}
	
	public static function dateSub($field, $interval, $time_span) {
		return "DATE_SUB($field, INTERVAL $interval $time_span)";
	}
	
	public static function min($field) {
		return "MIN($field)";
	}
	
	public static function max($field) {
		return "MAX($field)";
	}
	
	public static function lower($field) {
		return "LOWER($field)";
	}
	
	public static function upper($field) {
		return "UPPER($field)";
	}
	
	/**
	 * Add a new comparsion to this search criteria.
	 * @param mixed $field The name of the search field, or a nested Criteria.
	 * @param string [$value=null] The value to compare
	 * @param string [$comparison=null] The comparison operator.
	 */
	public function add($field, $value=null, $comparison=null) {
		return $this->addAnd($field, $value, $comparison);
	}

	/**
	 * Add a new item to the where clause, preceded by AND operator.
	 * @param mixed $field The name of the search field, or a nested Criteria.
	 * @param string [$value=null] The value to compare
	 * @param string [$comparison=null] The comparison operator.
	 */
	public function addAnd($field, $value=null, $comparison=null) {
		if ($comparison === null) $comparison = self::EQUAL;
		return $this->doAdd($field, $value, $comparison, self::OP_AND, "where");
	}

	/**
	 * Add a new item to the where clause, preceded by OR operator.
	 * @param mixed $field The name of the search field, or a nested Criteria.
	 * @param string [$value=null] The value to compare
	 * @param string [$comparison=null] The comparison operator.
	 */
	public function addOr($field, $value=null, $comparison=null) {
		if ($comparison === null) $comparison = self::EQUAL;
		return $this->doAdd($field, $value, $comparison, self::OP_OR, "where");
	}

	/**
	 * Add a new item to the having clause.
	 * @param mixed $field The name of the search field, or a nested Criteria.
	 * @param string [$value=null] The value to compare
	 * @param string [$comparison=null] The comparison operator.
	 */
	public function addHaving($field, $value=null, $comparison=null) {
		return $this->addAndHaving($field, $value, $comparison);
	}

	/**
	 * Add a new item to the having clause, preceded by AND operator.
	 * @param mixed $field The name of the search field, or a nested Criteria.
	 * @param string [$value=null] The value to compare
	 * @param string [$comparison=null] The comparison operator.
	 */
	public function addAndHaving($field, $value=null, $comparison=null) {
		if ($comparison === null) $comparison = self::EQUAL;
		return $this->doAdd($field, $value, $comparison, self::OP_AND, "having");
	}

	/**
	 * Add a new item to the having clause, preceded by OR operator.
	 * @param mixed $field The name of the search field, or a nested Criteria.
	 * @param string [$value=null] The value to compare
	 * @param string [$comparison=null] The comparison operator.
	 */
	public function addOrHaving($field, $value=null, $comparison=null) {
		if ($comparison === null) $comparison = self::EQUAL;
		return $this->doAdd($field, $value, $comparison, self::OP_OR, "having");
	}

	/**
	 * Used internally to add new conditions.
	 * @param mixed $field The name of the search field, or a nested Criteria.
	 * @param string $value The value to compare
	 * @param string $comparison The comparison operator.
	 * @param string $andor AND or OR
	 * @param string $type The type such as WHERE or HAVING
	 */
	protected function doAdd($field, $value, $comparison, $andor, $type) {
		if ($field instanceof self) {
			$this->fields[$type][] = array(
				"field" => null,
				"value" => $field,
				"andor" => $andor,
				"comparison" => null);
		}
		else
		{
			$this->fields[$type][] = array(
				"field" => $field,
				"value" => $value,
				"andor" => $andor,
				"comparison" => $comparison);
		}
	}

	/**
	 * Add a table to this criteria.
	 * @param string $class Table name (class name)
	 */
	public function addTable($class) {
		if (!Nina_Orm::isTable($class)) {
			throw new Nina_Exception_Orm("Cannot use table " . $class . " in Criteria.	I can't find this table.");
		}

		$table = new $class();
		$this->tables[$class] = $table->getTableName();
	}

	/**
	 * Add a subquery as the from field.
	 * @param Nina_Orm_Subquery $subquery The subquery
	 */
	public function addFromSubquery($subquery) {
		$this->table_subqueries[$subquery->getClass()] = '(' . $subquery->createStatement() .')';
	}
	
	/**
	 * Is this a select from subquery statement?
	 * @return boolean
	 */
	public function isSelectFromSubquery() {
		return (count($this->table_subqueries) > 0);
	}

	/**
	 * Get all tables in this criteria.
	 * @return array
	 */
	public function getTables() {
		return array_values($this->tables);
	}

	/**
	 * Get all subqueries used as tables in this criteria.
	 * @return array
	 */
	public function getTableSubqueries() {
		return array_values($this->table_subqueries);
	}

	/**
	 * Set the LIMIT points.
	 * If $b is passed, $a becomes an offset, otherwise $a is the limit.
	 * @param integer $a
	 * @param integer [$b=false]
	 */
	public function setLimit($a, $b = false) {
		if ($b) {
			$this->setOffset($a);
			$this->limit = (int) $b;
		}
		else {
			$this->limit = (int) $a;
		}
	}

	/**
	 * Get the LIMIT size.
	 * @return int
	 */
	public function getLimit() {
		if ($this->limit > -1) {
			return $this->limit;
		}
		return false;
	}

	/**
	 * Set the offset used in the LIMIT clause.
	 * @param integer $offset Offset
	 */
	public function setOffset($offset) {
		$this->offset = (int) $offset;
	}

	/**
	 * Get the offset used in the LIMIT clause.
	 * @return int
	 */
	public function getOffset() {
		return $this->offset;
	}

	/**
	 * Add a new field to order the results by.
	 * @param string $field Field name
	 * @param string [$dir=self::ORDER_ASC] Constant DESC or ASC from Nina_Orm_Criteria
	 */
	public function addOrderBy($field, $dir=self::ORDER_ASC) {
		$this->order[$field] = $dir;
	}


	/**
	 * Add a new field to order the results by.
	 * @param string $field Field name
	 * @param string [$dir=self::ORDER_ASC] Constant DESC or ASC from Nina_Orm_Criteria
	 */
	public function orderBy($field, $dir=self::ORDER_ASC) {
		$this->addOrderBy($field, $dir);
	}

	/**
	 * Add a new field to order the results by.
	 * @param string $field Field name
	 * @param string [$dir=self::ORDER_ASC] Constant DESC or ASC from Nina_Orm_Criteria
	 */
	public function addNatOrderBy($field, $dir=self::ORDER_ASC) {
		$this->nat_order[$field] = $dir;
	}


	/**
	 * Add a new field to order the results by.
	 * @param string $field Field name
	 * @param string [$dir=self::ORDER_ASC] Constant DESC or ASC from Nina_Orm_Criteria
	 */
	public function natOrderBy($field, $dir=self::ORDER_ASC) {
		$this->addOrderBy($field, $dir);
	}

	/**
	 * Get all ordering information as an associative array field => direction.
	 * @return array
	 */
	public function getOrderBy() {
		return $this->order;
	}

	/**
	 * Get all ordering information as an associative array field => direction.
	 * @return array
	 */
	public function getNatOrderBy() {
		return $this->nat_order;
	}

	/**
	 * Add a new field to group the results by.
	 * @param string $field Field name
	 */
	public function addGroupBy($field) {
		$this->group[] = $field;
	}

	/**
	 * Get all grouping information as an array
	 * @return array
	 */
	public function getGroupBy() {
		return $this->group;
	}
	
	/**
	 * Set a field to be returned as a min
	 * @param string $field Field name
	 */
	public function setMin($field) {
		$this->special_selects[$field] = 'MIN';
	}
	
	/**
	 * Set a field to be returned as a max
	 * @param string $field Field name
	 */
	public function setMax($field) {
		$this->special_selects[$field] = 'MAX';
	}
	
	/**
	 * Set a field to be returned as a count
	 * @param string $field Field name
	 */
	public function setCount($field) {
		$this->special_selects[$field] = 'COUNT';
	}
	
	/**
	 * Set a field to be returned as a sum
	 * @param string $field Field name
	 */
	public function setSum($field) {
		$this->special_selects[$field] = 'SUM';
	}
	
	/**
	 * Get the special select fields
	 * @return array
	 */
	public function getSpecialSelects() {
		return $this->special_selects;
	}
	
	/**
	 * Replace a field name with its special select
	 * @param string $field Field name
	 * @param boolean [$unset=false] Unset after replacing
	 * @return string
	 */
	public function replaceSelectField($field, $unset = false) {
		if(isset($this->special_selects[$field])) {
			$field = $this->special_selects[$field]."($field)";
			unset($this->special_selects[$field]);
		}

		return $field;
	}

	/**
	 * Add a new join to the search criteria.
	 * @param array|string $left Left column as (ClassName, FieldName)
	 * @param array|string $right Right column as (ClassName, FieldName)
	 * @param string [$type=Nina_Orm_Join::INNER] The join type (INNER, LEFT etc) from Nina_Orm_Join constants.
	 * @param boolean [$override=true] Override
	 * @throws Nina_Exception_Orm If bad mapping data is given - Yo Bitch.
	 */
	public function addJoin($left, $right, $type = Nina_Orm_Join::INNER, $override = true) {
		$join = Nina_Orm::newJoin($left, $right, $type);
		
		if(is_array($left))
			$left = array_values($left);
		else
			$left = explode('::', $left);
	
		$this->addTable($left[0]);
		if (!array_key_exists($join->getLeftTable(), $this->joins)) {
			$this->joins[$join->getLeftTable()] = array();
		}
		//If it's already set and we've requested not to override a default, then stop
		if (array_key_exists($join->getRightTable(), $this->joins[$join->getLeftTable()])
			&& !$override) {
			return false;
		}
		$this->joins[$join->getLeftTable()][$join->getRightTable()] = $join;
		return true;
	}

	/**
	 * Get all fields stored here.
	 * @return array
	 */
	public function getFields() {
		return $this->fields;
	}

	/**
	 * Get all joins stored here.
	 * @return array
	 */
	public function getJoins() {
		return $this->joins;
	}

	/**
	 * Set the database access object
	 * @param Nina_Db $db Db object
	 */
	public function setDb(Nina_Db $db) {
		$this->db = $db;
	}

	/**
	 * Get the database access object
	 * @return Nina_Db
	 */
	public function getDb() {
		return $this->db;
	}

	/**
	 * Used internally to render SQL for a series of conditions in WHERE or HAVING.
	 * @param string $type Type of clause
	 * @param string [$default=null] Default return value if result is empty
	 * @return string
	 */
	protected function doConditions($type, $default = null) {
		$fields = array();
		$in_loop = false;

		foreach ($this->fields[$type] as $field) {
			$data = "";
			if ($in_loop) {
				$data .= $field['andor'] . " ";
			}

			//Handle recursive sub-criteria
			if ($field['value'] instanceof self) {
				$c = clone $field['value'];
				$c->setDb($this->getDb());
				$data .= "(" . $c->createWhereClause(true) . ")";
				$this->bind[$type][] = $c;
			}
			//Render and insert a subquery
			elseif ($field['value'] instanceof Nina_Orm_Subquery) {
				$data .= $field['field'] . " " . $field['comparison'] . "(" . $field['value']->createStatement() . ")";
			}
			//Fields for comparison need to avoid being bound with quotes
			elseif($field['comparison'] == self::FIELD_COMPARISON) {
				$data .= $field['field'] . " = " . $field['value'];
			}
			//Fulltext MATCH
			elseif($field['comparison'] == self::MATCH) {
				$field_name = (is_array($field['field'])) ? implode(', ', $field['field']) : $field['field'];
				$data .= 'MATCH (' . $field_name . ") AGAINST (?)";
				$this->bind[$type][] = $field['value'];
			}
			//Fulltext MATCH
			elseif($field['comparison'] == self::MATCH_PHRASE) {
				$field_name = (is_array($field['field'])) ? implode(', ', $field['field']) : $field['field'];
				$data .= 'MATCH (' . $field_name . ") AGAINST (? IN BOOLEAN MODE)";
				$this->bind[$type][] = $field['value'];
			}
			//Fulltext MATCH
			elseif($field['comparison'] == self::MATCH_BOOLEAN) {
				$field_name = (is_array($field['field'])) ? implode(', ', $field['field']) : $field['field'];
				$data .= 'MATCH (' . $field_name . ") AGAINST (? IN BOOLEAN MODE)";
				$this->bind[$type][] = $field['value'];
			}
			//Numerical 'between' conditions
			elseif (in_array($field['comparison'], array(self::BETWEEN, self::NOT_BETWEEN))) {
				if (!is_array($field['value']) || count($field['value']) != 2) {
					throw new Nina_Exception_Orm("BETWEEN comparison used without array of size 2. Cannot continue.");
				}

				$btwn = array();
				foreach ($field['value'] as $v) {
					$this->bind[$type][] = $v;
					$btwn[] = "?";
				}

				$data .= $field['field'] . " " . $field['comparison'] . " " . implode(" AND ", $btwn);
			}
			//'In' conditions with array arguments
			elseif (in_array($field['comparison'], array(self::IN, self::NOT_IN)) || is_array($field['value'])) {
				if (!in_array($field['comparison'], array(self::IN, self::NOT_IN))) {
					$field['comparison'] = self::IN;
				}

				$field['value'] = (array) $field['value'];
				$values = array();
				foreach ($field['value'] as $f) {
					if ($f !== NULL) {
						$this->bind[$type][] = $f;
						$values[] = "?";
					}
					else
						$values[] = "NULL";
				}
				$data .= $field['field'] . " " . $field['comparison'] . " (" . implode(", ", $values) . ")";
			}
			//Wildcard conditions
			elseif ($field['comparison'] == self::LIKE_WILD) {
				$v = "?";
				$this->bind[$type][] = '%'.$field['value'].'%';
				$data .= $field['field'] . " LIKE " . $v;
			}
			//Plain equality tests
			else {
				if ($field['value'] !== NULL) {
					$v = "?";
					$this->bind[$type][] = $field['value'];
				}
				else
					$v = "NULL";

				$data .= $field['field'] . " " . $field['comparison'] . " " . $v;
			}
			$fields[] = $data;
			$in_loop = true;
		}

		$ret = implode(PHP_EOL, $fields);

		if (empty($ret)) $ret = $default;
		return $ret;
	}

	/**
	 * Prepare SQL to be added after the WHERE clause.
	 * @param boolean [$only_where=false] Only generate the WHERE clause (no order by etc).
	 * @return string
	 */
	public function createWhereClause($only_where = false) {
		$ret = $this->doConditions("where", 1);
		
		if (!$only_where && $group = $this->createGroupByClause()) {
			$ret .= PHP_EOL . "GROUP BY" . PHP_EOL . $group;
		}
		
		if (!$only_where && $having = $this->createHavingClause()) {
			$ret .= PHP_EOL . "HAVING" . PHP_EOL . $having;
		}
		
		if (!$only_where && $order = $this->createOrderByClause()) {
			$ret .= PHP_EOL . "ORDER BY" . PHP_EOL . $order;
		}
		
		if (!$only_where && $limit = $this->createLimitClause()) {
			$ret .= PHP_EOL . "LIMIT " . $limit;
		}

		return $ret;
	}

	/**
	 * Create the SQL which goes in the FROM clause.
	 * @return string
	 */
	public function createFromClause() {
		$from = array();
		$joins = $this->getJoins();
		$joined = array();
		
		if($this->isSelectFromSubquery()) {
			$subquery_count = 1;

			foreach($this->getTableSubqueries() as $subquery) {
				$from[] = $subquery . ' as `subquery_' . $subquery_count++ .'`';
			}
		}
		else {
			foreach ($this->getTables() as $table) {
				if (array_key_exists($table, $joins))
					$add = $this->doJoin($table, true, $joined);
				else
					$add = $table;
				
				if ($add) $from[] = $add;
			}
		}

		return implode("," . PHP_EOL, $from);
	}

	/**
	 * Create the SQL which goes after the ORDER BY clause.
	 * @return string
	 */
	public function createOrderByClause() {
		if (count($this->getOrderBy()) || count($this->getNatOrderBy())) {
			$order = array();
			foreach ($this->getNatOrderBy() as $field => $dir) {
				$order[] = $this->isSelectFromSubquery() ? 'LENGTH(`' . $field . '`) ' . $dir . ', `' . $field . '` ' . $dir : 'LENGTH(' . $field . ') ' . $dir .', ' . $field . ' ' . $dir;
			}
			foreach ($this->getOrderBy() as $field => $dir) {
				$order[] = $this->isSelectFromSubquery() ? '`' . $field . '` ' . $dir : $field . ' ' . $dir;
			}
			return implode(", ", $order);
		}
		else {
			//Default to sorting by sequence column if table has one
			if(count($this->tables)) {
				foreach (array_keys($this->tables) as $table) {
					if(defined("$table::SEQUENCE"))
						return constant("$table::SEQUENCE") . ' ' . self::ORDER_ASC;
				}
			}
		}
	}

	/**
	 * Create the SQL which goes after the GROUP BY clause.
	 * @return string
	 */
	public function createGroupByClause() {
		if (count($this->getGroupBy())) {
			$group = array();
			foreach ($this->getGroupBy() as $field) {
				$group[] = $this->isSelectFromSubquery() ? '`' . $field . '`' : $field;
			}
			return implode(", ", $group);
		}
	}

	/**
	 * Prepare the SQL which appears in the HAVING clause.
	 * @return string
	 */
	public function createHavingClause() {
		return $this->doConditions("having");
	}

	/**
	 * Generate the LIMIT clause for the SQL.
	 * @return string
	 */
	public function createLimitClause() {
		if ($this->getLimit()) {
			if ($this->getOffset())
				return $this->getOffset() . ", " . $this->getLimit();
			else
				return $this->getLimit();
		}
	}

	/**
	 * Used internally to render a join.
	 * This gets quite complex when several tables join to each other in more than one way.
	 * @param string $table The table at the left of the join
	 * @param boolean [$include_left=true] If the table at the left of the join needs to be explicitly given
	 * @param array $joined A reference to an array used in tracking during a recursive join process
	 * @return string
	 */
	protected function doJoin($table, $include_left = true, array &$joined) {
		if (in_array($table, $joined)) //Already joined
			return;
	
		$ret = $include_left ? $table : "";
		$joined[] = $table;
		foreach ($this->joins[$table] as $right => $join) {
			$ret .= PHP_EOL . $join->getType() . " JOIN " .
				$right . PHP_EOL . "ON " . $join->getLeftField() . " = " . $join->getRightField();

			if (array_key_exists($right, $this->joins) && !in_array($right, $joined)) {
				$ret .= $this->doJoin($right, false, $joined);
			}
		}
		return $ret;
	}

	/**
	 * Bind values to any SQL we've generated.
	 * @param Nina_Db_Adaptor_Statement $stmt The statement
	 */
	public function commit(Nina_Db_Driver_Statement $stmt) {
	
		if (!empty($this->bind['where'])) {
			$bind = array();
			foreach ($this->bind['where'] as $k => $v) {
				if ($v instanceof self) {
					if (!empty($bind)) $stmt->bind($bind);
					$bind = array();
					$v->commit($stmt); //ask other criteria to bind
				}
				else
					$bind[] = $v;
			}
			if (!empty($bind)) $stmt->bind($bind);
		}
		if (!empty($this->bind['having'])) {
			$bind = array();
			foreach ($this->bind['having'] as $k => $v) {
				if ($v instanceof self) {
					if (!empty($bind)) $stmt->bind($bind);
					$bind = array();
					$v->commit($stmt); //ask other criteria to bind
				}
				else
					$bind[] = $v;
			}
			if (!empty($bind)) $stmt->bind($bind);
		}
	}
}
